from __future__ import annotations

from typing import Any, Dict, Optional

from ..agents.multi_agent_orchestrator import MultiAgentOrchestrator
from ..agents.column_title_agent import ColumnTitleAgent
from ..core.database import DatabaseUtils
from ..core.config import settings
from ..utils.ai_utils import AIClient


class AnalysisService:
    def __init__(self, orchestrator: Optional[MultiAgentOrchestrator] = None):
        self.orchestrator = orchestrator or MultiAgentOrchestrator()
        self.title_agent = ColumnTitleAgent()

    async def analyze_query(self, query: str, database_type: str = "mysql", connection_config: Optional[Dict[str, Any]] = None) -> Dict[str, Any]:
        # 快速处理元数据意图，直接返回表/字段信息，避免走大模型
        q_lower = (query or "").lower()
        try:
            # 列出所有表
            if any(k in q_lower for k in ["获取数据库表信息", "有哪些表", "表列表", "所有表", "库里有什么表", "tables", "show tables"]):
                tables = DatabaseUtils.get_target_tables()
                return {"sql": None, "data": {"tables": tables}, "row_count": len(tables)}

            # 查询表结构/字段
            if any(k in q_lower for k in ["表结构", "字段", "列", "schema", "元数据", "describe "]):
                tables = DatabaseUtils.get_target_tables()
                # 基于现有表名做简单匹配
                matched_table = None
                for t in tables:
                    if t and str(t).lower() in q_lower:
                        matched_table = t
                        break
                if matched_table:
                    schema = DatabaseUtils.get_target_table_schema(matched_table)
                    return {"sql": None, "data": {"table": matched_table, **(schema or {})}, "row_count": len((schema or {}).get("columns", []))}
                # 未显式提表名时，返回可选表清单提示前端二次选择
                return {"sql": None, "data": {"message": "请指定要查看结构的表名", "tables": tables}, "row_count": 0}
        except Exception:
            # 元数据分支兜底，即使失败也不阻断后续 NL2SQL 流程
            pass

        # 准备上下文：表与字段
        tables = DatabaseUtils.get_target_tables()
        schema_map: Dict[str, list[str]] = {}
        table_samples: Dict[str, list[Dict[str, Any]]] = {}
        for t in tables[:20]:  # 限制数量以控制提示长度
            schema = DatabaseUtils.get_target_table_schema(t)
            cols = []
            if isinstance(schema, dict):
                # 兼容不同返回结构
                if "columns" in schema and isinstance(schema["columns"], list):
                    # MySQL information_schema 风格
                    for col in schema["columns"]:
                        if isinstance(col, dict):
                            name = col.get("column_name") or col.get("COLUMN_NAME") or col.get("name") or col.get("Field")
                            if name:
                                cols.append(str(name))
                elif isinstance(schema.get("column_details"), list):
                    for col in schema["column_details"]:
                        name = col.get("Field") or col.get("column_name")
                        if name:
                            cols.append(str(name))
            schema_map[t] = cols
            # 采样少量数据，帮助模型理解字段含义
            try:
                table_samples[t] = DatabaseUtils.get_table_samples(t)
            except Exception:
                table_samples[t] = []

        # 基于列名与样本值，动态推断跨表可用的关联键（非写死规则）
        inferred_relationships: list[dict] = []
        try:
            def _sample_values(tab: str, col: str) -> set[str]:
                vals: set[str] = set()
                for row in table_samples.get(tab, [])[:50]:
                    v = row.get(col)
                    if v is not None:
                        vals.add(str(v))
                return vals

            table_list = list(schema_map.keys())
            for i in range(len(table_list)):
                for j in range(i + 1, len(table_list)):
                    t1, t2 = table_list[i], table_list[j]
                    cands1 = schema_map.get(t1, [])[:64]
                    cands2 = schema_map.get(t2, [])[:64]
                    for c1 in cands1:
                        for c2 in cands2:
                            same_suffix = (c1 == c2) or (c1.endswith("_id") and c2.endswith("_id")) or (c1.endswith("_key") and c2.endswith("_key"))
                            if not same_suffix:
                                continue
                            inter = _sample_values(t1, c1) & _sample_values(t2, c2)
                            score = (1 if c1 == c2 else 0) + (1 if len(inter) > 0 else 0)
                            if score > 0:
                                inferred_relationships.append({
                                    "left_table": t1,
                                    "left_col": c1,
                                    "right_table": t2,
                                    "right_col": c2,
                                    "score": score,
                                })
        except Exception:
            pass

        # 1) NL -> SQL （带表结构上下文）
        # 若未显式传入，则使用全局配置的目标数据库类型
        db_type = database_type or settings.target_db_type
        # 将推断出的候选关联键提供给模型，帮助其更稳健地选择 JOIN 列
        rel_text = ""
        if inferred_relationships:
            rel_pairs = ", ".join([f"{r['left_table']}.{r['left_col']} = {r['right_table']}.{r['right_col']}" for r in inferred_relationships[:50]])
            rel_text = f"\n候选关联键(可优先使用): {rel_pairs}"
        sql = self.orchestrator.generate_sql(query + rel_text, db_type=db_type, table_schemas=schema_map, table_samples=table_samples)

        # 2) 执行 SQL
        # PostgreSQL 兼容处理：将反引号改为双引号，避免语法错误
        final_sql = sql
        if (db_type or "").lower() == "postgresql" or (settings.target_db_type or "").lower() == "postgresql":
            final_sql = (sql or "").replace("`", '"')
        # 执行前：轻量 JOIN 校验与自动修复（在不改变查询语义前提下优先同名列）
        def _repair_joins(sql_text: str) -> str:
            import re
            # alias 映射
            alias_map: Dict[str, str] = {}
            for m in re.finditer(r"\b(from|join)\s+\"?([A-Za-z0-9_\-]+)\"?\s+(?:as\s+)?\"?([A-Za-z0-9_]+)\"?", sql_text, re.IGNORECASE):
                alias_map[m.group(3)] = m.group(2)

            def qualify(alias: str, col: str) -> str:
                return f'"{alias}"."{col}"' if alias else f'"{col}"'

            def repl(m: 're.Match[str]') -> str:
                left_full, right_full = m.group(1), m.group(2)
                def split(expr: str) -> tuple[str|None, str]:
                    e = expr.replace('"', '')
                    parts = e.split('.')
                    if len(parts) == 2:
                        return parts[0], parts[1]
                    return None, parts[-1]
                la, lc = split(left_full)
                ra, rc = split(right_full)
                lt = alias_map.get(la or '', la or '')
                rt = alias_map.get(ra or '', ra or '')
                # 如果某侧列不在其表字段里，尝试用另一侧同名列修正
                if lt in schema_map and lc not in schema_map[lt] and rt in schema_map and rc in schema_map[rt] and rc in schema_map[lt]:
                    lc = rc
                if rt in schema_map and rc not in schema_map[rt] and lt in schema_map and lc in schema_map[lt] and lc in schema_map[rt]:
                    rc = lc
                return f"ON {qualify(la or '', lc)} = {qualify(ra or '', rc)}"

        
            return re.sub(r"\bON\s+([\"A-Za-z0-9_\.]+)\s*=\s*([\"A-Za-z0-9_\.]+)", repl, sql_text, flags=re.IGNORECASE)

        final_sql = _repair_joins(final_sql)
        res = DatabaseUtils.execute_target_query(final_sql)
        if hasattr(res, "fetchall"):
            data = [dict(r._mapping) for r in res.fetchall()]
            # 附带列备注：基于返回列名在各表中匹配，优先精确命中
            try:
                comments_map = DatabaseUtils.get_column_comments_map()
            except Exception:
                comments_map = {}
            # 从 SQL 中粗略解析别名来源，用于生成更贴近语义的标题（如 dish1/dish2, pair_count 等）
            alias_source: Dict[str, str] = {}
            try:
                import re as _re
                # 抓取 SELECT ... FROM 之间的部分
                msel = _re.search(r"select\s+([\s\S]*?)\s+from\s", final_sql, _re.IGNORECASE)
                if msel:
                    select_part = msel.group(1)
                    # 匹配  expr AS alias / expr alias
                    for m in _re.finditer(r"([^,]+?)\s+(?:as\s+)?(\"?[A-Za-z_][A-Za-z0-9_]*\"?)\s*(?:,|$)", select_part, _re.IGNORECASE):
                        expr = m.group(1).strip()
                        alias = m.group(2).strip().strip('"')
                        if alias:
                            alias_source[alias] = expr
            except Exception:
                pass
            columns_meta = []
            if data:
                sample_row = data[0]
                for col_name in list(sample_row.keys()):
                    comment = ""
                    # 1) 精确匹配（遍历所有表）
                    for tbl, cmap in comments_map.items():
                        if col_name in cmap and cmap[col_name]:
                            comment = cmap[col_name]
                            break
                    # 2) 末尾列名匹配（处理诸如 table.column 或别名）
                    if not comment and "." in col_name:
                        pure = col_name.split(".")[-1]
                        for tbl, cmap in comments_map.items():
                            if pure in cmap and cmap[pure]:
                                comment = cmap[pure]
                                break
                    # 3) 基于 SQL 中解析到的别名来源推断
                    if not comment and col_name in alias_source:
                        src = alias_source.get(col_name, "").lower()
                        if "dish_name" in src or "dishes" in src:
                            comment = "菜品名称"
                        elif "city" in src:
                            comment = "城市"
                        elif "province" in src:
                            comment = "省份"
                        elif "count(" in src:
                            comment = "数量"
                        elif "max(" in src and ("discount" in src or "promotion" in src):
                            comment = "最大折扣"
                        elif "sum(" in src and ("amount" in src or "revenue" in src or "sales" in src):
                            comment = "总金额"
                        elif "sum(" in src:
                            comment = "总计"

                        # 3.1 聚合命名：函数（来源列备注）→ 如 求和（销售额）/计数（订单）/平均（价格）
                        if not comment:
                            try:
                                import re as _re
                                m = _re.search(r"\b(sum|count|avg|min|max)\s*\((.*?)\)", src, _re.IGNORECASE)
                                if m:
                                    func = m.group(1).lower()
                                    inner = m.group(2).strip().strip('"')
                                    # 忽略 *
                                    base_cn = ""
                                    if inner != "*":
                                        # 取列名后缀
                                        base_col = inner.split('.')[-1].strip('"')
                                        # 优先从数据库备注匹配
                                        for _tbl, _cmap in comments_map.items():
                                            if base_col in _cmap and _cmap[base_col]:
                                                base_cn = str(_cmap[base_col])
                                                break
                                        # 兜底基于英文名的关键词
                                        if not base_cn:
                                            lname = base_col.lower()
                                            if any(k in lname for k in ["amount", "revenue", "sales", "total"]):
                                                base_cn = "销售额" if "sales" in lname else "金额"
                                            elif any(k in lname for k in ["price", "base_price", "unit_price"]):
                                                base_cn = "价格"
                                            elif any(k in lname for k in ["qty", "quantity", "count", "num"]):
                                                base_cn = "数量"
                                            elif "discount" in lname or "promotion" in lname:
                                                base_cn = "折扣"
                                            elif "time" in lname or "date" in lname:
                                                base_cn = "时间"
                                            elif "name" in lname:
                                                base_cn = "名称"
                                            else:
                                                base_cn = base_col
                                    func_cn = {
                                        "sum": "求和",
                                        "count": "计数",
                                        "avg": "平均",
                                        "min": "最小",
                                        "max": "最大",
                                    }.get(func)
                                    if func_cn:
                                        comment = f"{func_cn}（{base_cn or '全部'}）"
                            except Exception:
                                pass
                    columns_meta.append({"name": col_name, "comment": comment})

            # 若仍有空备注，调用专用 Agent 生成易读标题（不依赖写死规则）
            if any((not (c.get("comment") or "").strip()) for c in columns_meta):
                try:
                    col_names = [c["name"] for c in columns_meta]
                    # 仅挑选表达式中涉及到的列备注，减少提示体积
                    referenced_cols = set()
                    for expr in (alias_source or {}).values():
                        for part in (expr or '').replace('"','').replace('`','').split():
                            if '.' in part:
                                referenced_cols.add(part.split('.')[-1])
                    brief_comments = {}
                    for _t, _cmap in comments_map.items():
                        for _c, _v in _cmap.items():
                            if _c in referenced_cols and _v:
                                brief_comments[_c] = _v

                    mapping = self.title_agent.generate_titles(
                        query=query,
                        final_sql=final_sql,
                        alias_expr=alias_source,
                        brief_comments=brief_comments,
                        col_names=col_names,
                    )
                    if isinstance(mapping, dict):
                        for c in columns_meta:
                            if not (c.get("comment") or "").strip():
                                title = mapping.get(c["name"]) or mapping.get(c["name"].split(".")[-1])
                                if title:
                                    c["comment"] = str(title)
                except Exception:
                    pass
            # 兜底：对仍为空的列名，基于通用模式生成可读标题（不依赖具体库字段）
            for c in columns_meta:
                if (c.get("comment") or "").strip():
                    continue
                raw = c.get("name") or ""
                pure = raw.split(".")[-1]
                low = pure.lower()
                title = ""
                # 特例：dish1/dish2 等
                if low.startswith("dish") and low[4:].isdigit():
                    title = f"菜品{low[4:]}"
                elif low in ("pair_count", "combo_count", "cooccur_count"):
                    title = "搭配次数"
                if (low.startswith("sum_") or low.startswith("total_")) and ("discount" in low or "promotion" in low):
                    title = "总折扣"
                elif low.startswith("sum_") or low.startswith("total_"):
                    if any(k in low for k in ["amount", "revenue", "sales", "payment"]):
                        title = "总金额"
                    elif any(k in low for k in ["qty", "quantity", "count"]):
                        title = "总数量"
                    else:
                        title = "总计"
                elif (low.startswith("avg_") or low.startswith("average_")) and ("discount" in low or "promotion" in low):
                    title = "平均折扣"
                elif low.startswith("count_") or low == "count":
                    title = "数量"
                elif low.startswith("avg_") or low.startswith("average_"):
                    title = "平均值"
                elif low.startswith("max_"):
                    title = "最大折扣" if ("discount" in low or "promotion" in low) else "最大值"
                elif low.startswith("min_"):
                    title = "最小值"
                elif low.endswith("_name"):
                    title = pure[:-5] + "名称"
                if title:
                    c["comment"] = title
            return {"sql": final_sql, "data": data, "row_count": len(data), "columns": columns_meta}
        else:
            return {"sql": final_sql, "data": {"affected_rows": getattr(res, "rowcount", 0)}, "row_count": getattr(res, "rowcount", 0), "columns": []}


